import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.awt.Point;

public class DatabaseConnection {

    public Connection conn;
    private String user, pwd, dbName;

    public DatabaseConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException e) {
            System.out.println("Cannot find driver");
        }
        
        //I couldn't get dbprop to work, so here are the values hard coded
        pwd = "team4";
        user = "team4";
        dbName = "team4";
        conn = null;
        
        /*try {
            Properties p = new Properties();
            p.load(new FileInputStream("src/dbprop.txt"));
            pwd = p.getProperty("password");
            user = p.getProperty("user");
            dbName = p.getProperty("db_name");
            conn = null;
            
        } 
        catch (IOException e) {
            System.err.println("Could not find the properties file");
            System.exit(1);
        }*/
    }

    /**
     * Function used to connect to our database
        *
     */
    public void connect() {
        if (conn == null) {
            try {
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3307/" + dbName
                        + "?user=" + user + "&password=" + pwd);
            } catch (SQLException e) {
                System.err.println("Problem connecting to the database");
                conn = null;
            }
        }
    }

    /**
     * Closes the connection to the database if one exists
        *
     */
    public void closeConnection() {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                System.err.println("Problem closing the connection to the database");
            } finally {
                conn = null;
            }
        }
    }

    /**
     * Executes the sql query given on the current database
     *
     * @params: The sql query to execute @returns: The results of the sql query,
     * if the result is null or error occurs a null pointer is returned
        *
     */
    public ResultSet executeQuery(String query) {
        ResultSet resSet = null;
        if (conn == null) {
            connect();
        }
        try {
            Statement stmt = conn.createStatement();
            resSet = stmt.executeQuery(query);
            if (resSet.next()) {
                resSet.previous();
                System.out.println("Results found!!!! " + resSet.toString());
            } else {
                System.err.println("Empty set returned");
                resSet = null;
            }
        } catch (SQLException e) {
            System.out.println("This is the problem: " + e.getLocalizedMessage());
            resSet = null;
        } finally {
            return resSet;
        }
    }

    /**
     * Executes the sql insert, delete, or update given on the current database
     *
     * @params: The sql statement to execute @returns: -1 if there was an error
     * 0 if 0 rows were affected/no return >0 giving the new row count for the
     * table affected
        *
     */
    public int executeUpdate(String query) {
        int result = 0;
        if (conn == null) {
            connect();
        }
        try {
            Statement stmt = conn.createStatement();
            result = stmt.executeUpdate(query);
            
        } catch (SQLException e) {
            result = -1;
        } finally {
            return result;
        }
    }
    
    /**
        Saves the games current image to the database 
        Note: This also requires deleting the previous image
    **/
    public boolean saveImage(Game game) {
        boolean result = false;
        Image image = game.getImage();
        ArrayList<Point> keys = image.getKeys();
        int x, y;
        ImagePoint imageInfo;
        
        String user1 = game.getFirstPlayer() ? game.getLocalPlayer() : game.getRemotePlayer();
        String user2 = game.getFirstPlayer() ? game.getRemotePlayer() : game.getLocalPlayer();
        
        this.connect();
  
        // Get the br_num for the last set of events
        String lastSetEventsQuery = String.format(
            "SELECT DISTINCT br_num AS num FROM game_events WHERE user1 = '%s' AND user2 = '%s'",
            user1, user2
        );

        ResultSet lastBrNumSet = this.executeQuery(lastSetEventsQuery);
        int lastBrNum = -1;
        
        if (lastBrNumSet != null) {
            try {
                lastBrNumSet.next();
                lastBrNum = lastBrNumSet.getInt("num");
                System.out.println("last game num: " + lastBrNum);
            }
            catch (SQLException e) {
                System.err.println("Good not find the last set of game events");
            }
        }

        int insertResult = 0;

        for (int i = 0; i < keys.size(); i++) {
            x = keys.get(i).x;
            y = keys.get(i).y;
            imageInfo = image.getImageInfoAtPos(x,y);

            String insertEventStatement = String.format(
                "INSERT INTO game_events(`user1`, `user2`, `br_x`, `br_y`, `br_color`, `br_size`, `order`) "
                    + "VALUES('%s', '%s', %d, %d, %d, %d, %d)",
                user1, user2, x, y, imageInfo.getColor(), imageInfo.getSize(), imageInfo.getOrder()
            );

            insertResult = this.executeUpdate(insertEventStatement);

            if (insertResult < 0) {
                System.err.println("Problem inserting point: " + x + ", " + y);
                System.err.println(insertEventStatement);
                break;
            }
        }

        if (insertResult > -1) {
            if (lastBrNum != -1) {
                String deleteOldEvents = String.format(
                    "DELETE FROM game_events WHERE br_num=%d AND user1='%s' AND user2='%s'",
                    lastBrNum, user1, user2
                );
                
                this.executeUpdate(deleteOldEvents);
            }

            // update the game state to represent the change that is being made
            String advanceStateUpdate = String.format(
                "UPDATE game_state SET state='%s', player='%d' WHERE user1='%s' AND user2='%s'",
                "guess", game.getFirstPlayer() ? 2 : 1, user1, user2
            );

            this.executeUpdate(advanceStateUpdate);
            
            result = true;
        }
        
        this.closeConnection();
        
        return result;
    }
}